

*********************************************
* Calculate variables from Ficus-Fare
*********************************************
use "${file}ficusfare.dta", clear // Aggregated all Ficus_fare files from 2002-2017
sort SIREN Y 
order SIREN Y APE1 APE2
label var APE1 "APE rev1 liasse"
label var APE2 "APE rev2 liasse"

* put some variables to zero if missing
foreach v of varlist AUTCAPI SUBVINV AUTFOND ///
DETFOUR AUTDETT AVANCOU DETTEFI {
replace `v'=0 if `v'==.
}

* date creation
gen DATCRY = substr(DATCR, 1,4)
gen DATCRM = substr(DATCR, 5,2)
gen DATCSY = substr(DATCS, 1,4)
gen DATCSM = substr(DATCS, 5,2)
replace DATCSY="" if length(DATCSY)<4
destring DATCRY DATCRM DATCSY DATCSM, replace force

* Equity and Debt
replace DETTETOT=DETTEFI+DETFOUR+AUTDETT-AVANCOU
gen CAPIPRO=CAPISOC+AUTCAPI+SUBVINV+AUTFOND
replace CAPIPRO=. if CAPIPRO==0

* Total assets
replace TACTINT=. if TACTINT<0
gen lnasset= ln(TACTINT)

* Cash
replace TRESACT=0 if TRESACT<0

* Ratios
gen debtratio=(DETTETOT)/(CAPIPRO+DETTETOT)
gen leverage=(DETTETOT)/(CAPIPRO)
gen debtratio2=(DETTEFI)/(CAPISOC+DETTEFI)
gen leverage2=(DETTEFI)/(CAPISOC)
gen debtfiratio=DETTEFI/DETTETOT
gen debtfi=DETTEFI/DETTETOT
gen tangible=ACTANG/TACTINT
gen salesext=CAEXP/CA
gen cashratio = TRESACT/TACTINT
gen roa=RESULTA/TACTINT 
gen roe=RESULTA/CAPISOC 
winsor2 roa roe, replace cuts(1 99) by(Y) 

* Employment size in balance-sheets (might be different from DADS)
replace EFF=. if EFF<0

* sales
rename CA sales
replace sales=0 if sales<0
gen lnsales=ln(1+sales)
gen soa=sales/TACTINT
gen soeff=sales/EFF
gen roeff=RESULTA/EFF

* delta sales
egen siren_num=group(SIREN)
duplicates drop siren_num Y, force
xtset siren_num Y
gen dsales=(sales-l.sales)/l.sales

* keep relevant variables
keep SIREN YEAR Y EFF APE* CTCX  ///
DATCRM DATCSY DATCSM  ///
leverage debtratio leverage2 debtratio2 debtfi debtfiratio ///
tangible cashratio sales lnsales dsales salesext lnasset soeff ///
DATCRY DATCRM DATCSY DATCSM RESULTA REX RECOURAN EBE roa roe  soa ///
CAPISOC AUTCAPI SUBVINV AUTFOND TACTINT TRESACT DIVIDEN INVCORP INVTOT 

* label variables
label var leverage "D/E"
label var debtratio "D/(D+E)"
label var debtfi "Debt fin./total debt"
label var lnasset "Ln(total assets)"
label var tangible "Tangible/total assets"
label var RESULTA "Income" 
label var REX "EBIT"
label var RECOURAN "Current Income" 
label var EBE "EBITDA"
label var cashratio "Cash/total assets"
label var sales "Sales"
label var lnsales "Log(Sales)"
label var dsales "Delta(Sales)"
label var TACTINT "Total assets"
label var INVTOT "Investment"
label var TRESACT "Cash"
label var roa "ROA"
label var roe "ROE"
label var soeff "Sales/N.employees"
label var soa "Sales/total assets"
label var salesext "Sales abroad/Total sales"
label var EFF "N.employees (liasse)"

sort SIREN Y 
save "${file}ficusfare_all.dta", replace

* Adjust Sectors nomenclatures
use "${file}ficusfare_all.dta", clear
gen APE =  APE2
replace APE = APE1 if APE==""
order SIREN Y APE APE1 APE2

* complete missing sectors with upper years sector for firms created in 2002
bys SIREN: replace APE = APE[_n+1] if Y==2002&Y[_n+1]==2003 // retreatment for sectors in 2002 (rev 1 to rev 2)
count if Y==2002&length(APE1)==4
merge m:1 APE1 using  "${file}ape_rev12.dta", update // convert APE rev 1 to APE rev 2
drop if _merge ==2
replace APE = APE2 if length(APE)==4&_m==4
drop _m

save "${file}ficusfare_all.dta", replace
  